# -*- coding:utf-8 -*-

import configparser
import xlsxwriter
import copy

from datetime import datetime, timedelta


class create_user_excel(object):
    def __init__(self):
        self.config_file = r'conf\\config.ini'
        self.config = configparser.ConfigParser()
        self.config.read(self.config_file, encoding="utf-8")

    def active_users(self):
        active1 = []
        active2 = []
        a_title = '活跃用户统计'
        a_h1 = ['日期', '活跃用户数量']
        active_file = self.config.get('file', 'active_file')
        with open(active_file, 'r') as e:
            for i in e.readlines():
                a = i.strip()
                active1.append(a.split()[0])
                active2.append(int(a.split()[1]))
        e.close()
        return active1, active2, a_title, a_h1

    def generate_excel(self,title, shop_list_data, other_list_data, company_list, company_data):
        new_date = datetime.now().strftime("%Y%m%d")
        now_date = datetime.now()
        before_data1 = now_date + timedelta(days=-1)
        before_data2 = before_data1.strftime('%Y/%m/%d')
        today_data = datetime.now().strftime("%Y/%m/%d")
        ##创建xlsx状态
        workbook = xlsxwriter.Workbook(r"file\\%s_user.xlsx" % new_date)
        worksheet = workbook.add_worksheet()

        ##格式配置
        worksheet.set_column('A:N', 20)
        # 标题
        format_title = workbook.add_format({'font_size': 20, 'bold': True, 'border': 1})
        format_title.set_align('center')
        format_title.set_align('vcenter')

        # 第一行
        format_h1 = workbook.add_format({'font_size': 12, 'bold': True, 'border': 1})
        format_h1.set_align('center')
        format_h1.set_align('vcenter')

        # 第二行
        format_h2 = workbook.add_format(
            {'font_size': 12, 'bold': True, 'border': 1, 'font_color': '#FF0000', 'bg_color': '#FFFF00'})
        format_h2.set_align('center')
        format_h2.set_align('vcenter')

        # 内容
        format_data = workbook.add_format({'font_size': 10, 'border': 1})
        format_data.set_align('center')
        format_data.set_align('vcenter')

        # 标题二
        format_title_2 = workbook.add_format(
            {'font_size': 12, 'bold': True, 'border': 1, 'font_color': '#FF0000', 'bg_color': '#FFFF00'})
        format_title_2.set_align('center')
        format_title_2.set_align('vcenter')

        # 整合数据
        h2_data1 = ['运营商']
        h2_data2 = ['导入店铺数量', '上线店铺数量', '臻E盾用户数', '臻中心用户数', '臻护卫用户数', '臻总管用户数']
        h2_e = ['B3:C3', 'D3:E3', 'G3:H3', 'I3:J3', 'K3:L3', 'M3:N3']
        h2_data3 = ['备注']

        # 创建表格
        worksheet.merge_range('A1:O1', title, format_title)
        worksheet.write_row('A2', shop_list_data, format_h1)
        worksheet.write_row('A3', h2_data1, format_h2)
        worksheet.write_row('F3', ['未绑定用户店铺数'], format_h2)
        for i in range(len(h2_data2)):
            worksheet.merge_range(h2_e[i], h2_data2[i], format_h2)

        worksheet.write_row('O3', h2_data3, format_h2)
        n = 4
        start = 4
        user_sum = []
        for i in company_list:
            data_list = copy.deepcopy(company_data[i])
            try:
                user_sum.append(sum([int(ge_i) for ge_i in data_list[4::2]]))
            except:
                print(data_list[4::2])
            data_list.insert(0, i)
            data_list.append("")
            worksheet.write_row('A%s' % n, data_list, format_data)
            n += 1
        worksheet.write(n - 1, 0, '合计', format_data)
        worksheet.write(n - 1, 7, '', format_data)
        excel_list = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N','O']
        for i in range(13):
            worksheet.write(n - 1, i + 1, '=SUM(%s%d:%s%d)' % (excel_list[i], start, excel_list[i], n - 1), format_data)

        end_list = other_list_data
        worksheet.write_row('A%s' % (n + 1), end_list, format_data)
        chart1 = workbook.add_chart({'type': 'column'})

        active1, active2, a_title, a_h1 = self.active_users()
        worksheet.merge_range('A%d:B%d' % (n + 3, n + 3), a_title, format_title_2)
        worksheet.write_row('A%d' % (n + 4), a_h1, format_h2)
        worksheet.write_column('A%d' % (n + 5), active1, format_data)
        worksheet.write_column('B%d' % (n + 5), active2, format_data)
        chart2 = workbook.add_chart({'type': 'column'})

        chart2.add_series({
            'name': '=Sheet1!$B$%d' % (n + 4),
            'categories': '=Sheet1!$A$%d:$A$%d' % (n + 5, n + 11),
            'values': '=Sheet1!$B$%d:$B$%d' % (n + 5, n + 11),
        })

        chart2.set_title({'name': 'Active users'})

        chart2.set_x_axis({'name': 'Date'})

        chart2.set_y_axis({'name': 'Number'})

        chart2.set_style(11)

        worksheet.insert_chart('C%d' % (n + 2), chart2, {'x_offset': 35, 'y_offset': 10})

        workbook.close()